﻿using CBS.Common;
using CBS.DB.CBSEntity;
using CBS.DB;
using CBS.Model.ManageModel;
using CBS.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Data.Common;
using MySqlConnector;
using CBS.Model.UIModel;

namespace CBS.APP
{
    public class LessonApp
    {
        public static bool CreateLesson(int coursePlanId, int teacherId, decimal lessonTimeLong, List<DateTime> startTime)
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var coursePlan = cbs.cbs_course_plan.FirstOrDefault(t => t.CoursePlanId == coursePlanId);
                if (coursePlan == null)
                {
                    transaction.Rollback();
                    return false;
                }

                var data = startTime.Select(t => new cbs_lesson { CoursePlanId = coursePlanId, TeacherId = teacherId, StartTime = t, EndTime = t.AddHours(Convert.ToDouble(lessonTimeLong)), LessonTimeLong = lessonTimeLong }).ToList();
                cbs.cbs_lesson.AddRange(data);

                cbs.SaveChanges();
                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }


        public static List<LessonViewModel> GetList(Pagination page, int semesterId, int gradeId, int classId, int courseId, int teacherId, string coursePlan)
        {
            string sql = @"select a.*,b.LessonId,b.LessonTimeLong,b.StartTime,b.EndTime,c.TeacherName,d.SemesterName,e.GradeName,f.ClassName,g.CourseName
from cbs_course_plan a 
	inner join cbs_lesson b on a.CoursePlanId = b.CoursePlanId
	left join cbs_teacher c on b.TeacherId = c.TeacherId
	left join cbs_semester d on a.SemesterId = d.SemesterId
	left join cbs_Grade e on a.GradeId = e.GradeId
	left join cbs_class f on a.ClassId = f.ClassId
	left join cbs_course g on a.CourseId = g.CourseId
where a.SemesterId = @semesterId 
and (@gradeId = 0 or a.GradeId = @gradeId)
and (@classId = 0 or a.ClassId = @classId)
and (@courseId = 0 or a.CourseId = @courseId)
and (@teacherId = 0 or b.TeacherId = @teacherId)
and (@coursePlan = '' or a.CoursePlanName like @coursePlan)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@gradeId",gradeId),
                new MySqlParameter("@classId",classId),
                new MySqlParameter("@courseId",courseId),
                new MySqlParameter("@teacherId",teacherId),
                new MySqlParameter("@coursePlan",$"%{coursePlan}%"),
            };

            return DBHelper.FindListOrderBy<LessonViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static List<LessonSelectModel> GetSelectList(int semesterId)
        {
            using CBSContext cbs = new();
            var list = (from cp in cbs.cbs_course_plan
                        join s in cbs.cbs_lesson on cp.CoursePlanId equals s.CoursePlanId
                        join g in cbs.cbs_grade on cp.GradeId equals g.GradeId
                        join cla in cbs.cbs_class on cp.ClassId equals cla.ClassId
                        join cour in cbs.cbs_course on cp.CourseId equals cour.CourseId
                        where cp.SemesterId == semesterId
                        orderby cp.CoursePlanId, cp.GradeId, cp.ClassId, cp.CourseId, s.StartTime descending
                        select new LessonSelectModel
                        {
                            LessonId = s.LessonId,
                            LessonTime = cp.CoursePlanName + "-" + g.GradeName + "-" + cla.ClassName + "-" + cour.CourseName + "-" + s.StartTime.ToString("yyyy/MM/dd HH:mm")
                        }).ToList();

            return list;
        }

        public static List<LessonSelectModel> GetSelectList(int semesterId, int gradeId, int classId, int courseId)
        {
            using CBSContext cbs = new();
            var list = (from cp in cbs.cbs_course_plan
                        join s in cbs.cbs_lesson on cp.CoursePlanId equals s.CoursePlanId
                        join g in cbs.cbs_grade on cp.GradeId equals g.GradeId
                        join cla in cbs.cbs_class on cp.ClassId equals cla.ClassId
                        join cour in cbs.cbs_course on cp.CourseId equals cour.CourseId
                        where cp.SemesterId == semesterId
                        && (gradeId == 0 || cp.GradeId == gradeId)
                        && (classId == 0 || cp.ClassId == classId)
                        && (courseId == 0 || cp.CourseId == courseId)
                        orderby cp.CoursePlanId, cp.GradeId, cp.ClassId, cp.CourseId, s.StartTime descending
                        select new LessonSelectModel
                        {
                            LessonId = s.LessonId,
                            LessonTime = cp.CoursePlanName + "-" + g.GradeName + "-" + cla.ClassName + "-" + cour.CourseName + "-" + s.StartTime.ToString("yyyy/MM/dd HH:mm")
                        }).ToList();
            if (list != null)
            {
                return list??new List<LessonSelectModel>();
            }
            else
            {
                return new List<LessonSelectModel>();
            }
        }

        public static List<LessonSelectModel> GetLessonTimeSelects(int coursePlanId)
        {
            using CBSContext cbs = new();
            var list = (from cp in cbs.cbs_course_plan
                        join s in cbs.cbs_lesson on cp.CoursePlanId equals s.CoursePlanId
                        join g in cbs.cbs_grade on cp.GradeId equals g.GradeId
                        join cla in cbs.cbs_class on cp.ClassId equals cla.ClassId
                        join cour in cbs.cbs_course on cp.CourseId equals cour.CourseId
                        where cp.CoursePlanId == coursePlanId
                        orderby s.StartTime descending
                        select new LessonSelectModel
                        {
                            LessonId = s.LessonId,
                            LessonTime = s.StartTime.ToString("yyyy/MM/dd HH:mm")
                        }).ToList();

            return list;
        }

        public static bool DeleteLesson(int lessonId, int teacherId, out string msg)
        {
            msg = "";
            using CBSContext cbs = new();
            try
            {
                var less = cbs.cbs_lesson.FirstOrDefault(t => t.LessonId == lessonId);
                if (less == null)
                {
                    msg = "未找到将要删除的数据，请重试。";
                    return false;
                }

                if (cbs.cbs_student_sign_detail.Any(t => t.LessonId == lessonId))
                {
                    msg = "数据已经被使用，不能删除。";
                    return false;
                }

                cbs.cbs_lesson.Remove(less);

                cbs.SaveChanges();

                return true;
            }
            catch
            {
                throw;
            }
        }

        public static bool BatchDeleteLesson(int semesterId, int gradeId, int classId, int courseId, int teacherId, string coursePlan, out string msg)
        {
            msg = "";
            using CBSContext cbs = new();
            try
            {
                //var lessons = (from less in cbs.cbs_lesson
                //               join tea in cbs.cbs_teacher on less.TeacherId equals tea.TeacherId
                //               where less.CoursePlanId == coursePlanId && tea.TeacherName.Contains(teacherName)
                //               select less).ToList();

                

                var lessons = (from courp in cbs.cbs_course_plan
                               join less in cbs.cbs_lesson on courp.CoursePlanId equals less.CoursePlanId
                               join tea in cbs.cbs_teacher on less.TeacherId equals tea.TeacherId
                               where courp.SemesterId == semesterId
                               && (gradeId == 0 || courp.GradeId == gradeId)
                               && (classId == 0 || courp.ClassId == classId)
                               && (courseId == 0 || courp.CourseId == courseId)
                               && (teacherId == 0 || less.TeacherId == teacherId)
                               && (coursePlan == "" || courp.CoursePlanName.Contains(coursePlan))
                               select less).ToList();
                if (lessons.Count == 0)
                {
                    return false;
                }

                int[] lessonIds = lessons.Select(t => t.LessonId).ToArray();
                if (cbs.cbs_student_sign_detail.Any(t => lessonIds.Contains(t.LessonId)))
                {
                    msg = "数据已经被使用，不能删除。";
                    return false;
                }

                cbs.cbs_lesson.RemoveRange(lessons);
                cbs.SaveChanges();

                return true;
            }
            catch
            {
                throw;
            }
        }

        public static LessonViewModel GetForm(int lessonId)
        {
            using CBSContext cbs = new();
            var lesson = cbs.cbs_lesson.FirstOrDefault(t => t.LessonId == lessonId);
            var coursePlan = cbs.cbs_course_plan.FirstOrDefault(t => t.CoursePlanId == lesson.CoursePlanId);
            var classPlan = cbs.cbs_class.FirstOrDefault(t => t.ClassId == coursePlan.ClassId);
            var semester = cbs.cbs_semester.FirstOrDefault(t => t.SemesterId == coursePlan.SemesterId);
            var teacher = cbs.cbs_teacher.FirstOrDefault(t => t.TeacherId == lesson.TeacherId);

            return new LessonViewModel()
            {
                LessonId = lesson.LessonId,
                SemesterId = semester.SemesterId,
                SemesterName = semester.SemesterName,
                ClassId = classPlan.ClassId,
                ClassName = classPlan.ClassName,
                TeacherId = teacher.TeacherId,
                TeacherName = teacher.TeacherName,
                LessonTimeLong = lesson.LessonTimeLong,
                StartTime = lesson.StartTime,
                EndTime = lesson.EndTime,
            };
        }

        public static LessonViewModel GetForm(int semesterId, int classPlanId, int teacherId)
        {
            using CBSContext cbs = new();
            var coursePlan = cbs.cbs_course_plan.FirstOrDefault(t => t.SemesterId == semesterId && t.ClassId == classPlanId);
            var lesson = cbs.cbs_lesson.FirstOrDefault(t => t.CoursePlanId == coursePlan.CoursePlanId && t.TeacherId == teacherId);
            var classPlan = cbs.cbs_class.FirstOrDefault(t => t.ClassId == coursePlan.ClassId);
            var semester = cbs.cbs_semester.FirstOrDefault(t => t.SemesterId == semesterId);
            var teacher = cbs.cbs_teacher.FirstOrDefault(t => t.TeacherId == lesson.TeacherId);

            return new LessonViewModel()
            {
                LessonId = lesson.LessonId,
                SemesterId = semester.SemesterId,
                SemesterName = semester.SemesterName,
                ClassId = classPlan.ClassId,
                ClassName = classPlan.ClassName,
                TeacherId = teacher.TeacherId,
                TeacherName = teacher.TeacherName,
                LessonTimeLong = lesson.LessonTimeLong,
                StartTime = lesson.StartTime,
                EndTime = lesson.EndTime,
            };
        }
    }
}
